package com.ceshiren.springstudy;

import com.ceshiren.springstudy.dao.UserDao;
import com.ceshiren.springstudy.dao.impl.UserDaoImpl;
import com.ceshiren.springstudy.dto.TestUser;
import com.mysql.cj.jdbc.Driver;
import org.apache.commons.codec.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;


import java.io.InputStream;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

public class JdbcTestDemo {




    @Test
    public void test() throws ClassNotFoundException, SQLException {
        /**
         * 1.注册驱动
         * 2.获取连接
         * 3.获取statement
         * 4.执行sql查询语句
         * 5.结果集遍历
         * 6.封装
         * 7.释放资源
         */
        // 1、注册驱动
        //        问题一
        //1.数据库配置信息硬编码
        //解决思路：配置文件
        //2.频繁创建数据库连接
        //解决思路：数据库连接池
        //解决方案： 使用数据库连接池初始化连接资源
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.100.74:3306/itrus_cim?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&&autoReconnect=true&zeroDateTimeBehavior=convertToNull", "root", "password123");
        //3、获取statement
        Statement statement = connection.createStatement();
        //4、写sql，并执行sql
        //        问题二
        //1.存在SQL语句硬编码问题
        //解决思路：配置文件
        //解决方案：将sql语句抽取到xml配置文件中
        String sql = "select * from cim_user";
        ResultSet resultSet = statement.executeQuery(sql);
        //5、结果集遍历
        //1.手动封装返回结果集
        //解决思路：反射+内省
        //解决方案：查询操作时，需要手动将结果集中的数据手动封装到实体中
        ArrayList<TestUser> users = new ArrayList<TestUser>();
        while (resultSet.next()) {
            //6、封装
            TestUser testUser = new TestUser();
            testUser.setId(resultSet.getString("id"));
            testUser.setUserAccount(resultSet.getString("user_account"));
            testUser.setPassword(resultSet.getString("password"));
            testUser.setPhone(resultSet.getString("phone"));

            users.add(testUser);
        }
        users.forEach(user -> System.out.println(user));
        //7、释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }

    // 查询cs_user表中素有数据
    @Test
    public void jdbcTest2() {
        //1、加载mybatis核心配置文件，为stream流
        InputStream inputStream = Resources.getInputStream("SqlMapConfig.xml");
        //2、通过mybatis的数据库配置流，创建连接池
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //3、获取其中的一个连接
        SqlSession session = sessionFactory.openSession();
        //4、执行sql，传入对应唯一值，namespace.id，可以定位到对应的sql
        List<TestUser> users = session.selectList("user1.findAll");
        //5、输出遍历
        users.forEach(user -> System.out.println(user));
        //6、释放资源
        session.close();
    }


    // 添加用户
    @Test
    public void jdbcAddUserTest2() {
        //1、加载mybatis核心配置文件，为stream流
        InputStream inputStream = Resources.getInputStream("SqlMapConfig.xml");
        //2、通过mybatis的数据库配置流，创建连接池
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //3、获取其中的一个连接
        SqlSession session = sessionFactory.openSession();
        //4、执行sql，传入对应唯一值，namespace.id，可以定位到对应的sql
        TestUser user = new TestUser();
        user.setId("123321");
        user.setUserId("1231212312");
        user.setUserName("gaoxiaoqi");
        user.setUserKey("1q2w3e4r");
        user.setCreateTime(LocalDateTime.now());
        user.setUpdateTime(LocalDateTime.now());

        int insert = session.insert("user1.addUser", user);
        //5、输出遍历
        System.out.println(insert);
        // 事务提交
        session.commit();
        //6、释放资源
        session.close();
    }

    @Test
    public void findUserByIdTest(){
        InputStream inputStream = Resources.getInputStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sessionFactory.openSession();
        TestUser user = sqlSession.selectOne("user1.findUserById", "123321");
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void updateUserTest(){
        InputStream inputStream = Resources.getInputStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sessionFactory.openSession();
        TestUser user = new TestUser();
        user.setId("123321");
        user.setUserId("1234567890");
        user.setUserName("六六六");
        user.setUserKey("meiyouyoukey");
        user.setUpdateTime(LocalDateTime.now());
        int update = session.update("user1.updateUser", user);
        System.out.println(update);
        session.commit();
        session.close();
    }


    @Test
    public void delUser(){
        InputStream inputStream = Resources.getInputStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sessionFactory.openSession();
        int delete = sqlSession.delete("user1.delUser", "123321");
        System.out.println(delete);
        sqlSession.commit();
        sqlSession.close();
    }
}
